package com.chen106106.sstest.dao;

import java.text.SimpleDateFormat;

import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;

import java.util.Date;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;

import com.chen106106.sstest.common.memcached.MemcachedCache;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;

import com.chen106106.sstest.model.Article;
import java.util.List;

public class ArticleMgrDaoImpl implements ArticleMgrDao {

	private NamedParameterJdbcTemplate jdbcTemplate;
	
	private Logger log = Logger.getLogger(ArticleMgrDaoImpl.class);
	private ParameterizedBeanPropertyRowMapper<Article> rowMapper=ParameterizedBeanPropertyRowMapper.newInstance(Article.class);
	
	
	public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int add(final Article t) {
		if(null == t){
			return -1;
		}

		long articleId = -1;
		try {
			articleId =  this.jdbcTemplate.getJdbcOperations().execute(new ConnectionCallback<Long>() {
				@Override
				public Long doInConnection(Connection conn) throws SQLException,DataAccessException {
					
					String sql = "INSERT INTO mtsyd_article(title,content,release_date,source) values(?,?,?,?)";					
					PreparedStatement ps=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
					ps.setString(1,t.getTitle());
					ps.setString(2,t.getContent());
					
					SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
					String str = t.getReleaseDate()==null?null:sdf.format(t.getReleaseDate());
					ps.setString(3, str);
					ps.setString(4, t.getSource());
					ps.executeUpdate();
					
					long articleId = -1;
					ResultSet rs = ps.getGeneratedKeys();
			        if (rs.next()){
			        	articleId = rs.getInt(1);
			        }
			        return articleId;
				}
			});
		} catch (DataAccessException e) {
			log.error(e.getMessage(),e);
			return -1;
		}
	    
		return (int) articleId;
	}

	@Override
	public boolean delete(int id) {
		String sql = "UPDATE mtsyd_article set deleted = 1 WHERE id=?";
		log.info(sql);
		boolean result = false;
	    try{
			result = this.jdbcTemplate.getJdbcOperations().update(sql,id)>0?true:false;
	    }catch(Exception e){
	    	log.error(e.getMessage(),e);
	    	return false;
	    }
		
		return result;
	}

	@Override
	public boolean update(Article t) {
		String sql = "UPDATE mtsyd_article set title=?,content =? ,release_date =?,source=? WHERE id=?";
		if(null==t){
			return false;
		}
		log.info(sql);

		
	    try{
			boolean result = this.jdbcTemplate.getJdbcOperations().update(sql,t.getTitle(),t.getContent(),t.getReleaseDate(),t.getSource(),t.getId())>0?true:false;
			return result;
	    }catch(Exception e){
	    	log.error(e.getMessage(),e);
	    	return false;
	    }
		
	}

	@Override
	public Article queryById(int id) {
		String sql = "SELECT id,title,content,release_date,source FROM mtsyd_article WHERE id=? limit 1";
		log.info(sql);
		
		
	    try{
	    	Article a = this.jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{id}, rowMapper);
	    	return a;
	    }catch(Exception e){
	    	log.error(e.getMessage(),e);
	    	return null;
	    }
	}

	@Override
	public List<Article> queryList(int offSet, int pageSize) {
	    List<Article> articles = new ArrayList<Article>();
	    
	    try{
	    	String sql = "SELECT id,title,content,release_date,source FROM mtsyd_article ";
	 		StringBuilder sb = new StringBuilder();
	 		sb.append(sql);
	 	
	 		sb.append("LIMIT ");
	 		sb.append(offSet);
	 		sb.append(" ,");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
	 		sb.append(" "+pageSize);
	 		log.info(sb.toString());

	 		articles = this.jdbcTemplate.getJdbcOperations().query(sb.toString() ,rowMapper);
	    }catch(Exception e){
	    	log.error(e.getMessage(),e);
	    	return null;
	    }
	   
	    return articles;
	}

	@Override
	public int countArticleNum() {
		try{
			String sql = "SELECT COUNT(id) FROM mtsyd_article WHERE deleted =0 ";
			return this.jdbcTemplate.getJdbcOperations().queryForInt(sql);
		}catch(Exception e){
			log.error(e.getMessage(),e);
			return 0;
		}
	}

}
